﻿----------------------------SQL SERVER数据综合练习题----------------------------
--创建MySchoolDB数据库
IF DB_ID('MySchoolDB') IS NOT NULL	
BEGIN DROP DATABASE MySchoolDB END

CREATE DATABASE MySchoolDB
ON
(
NAME = 'MySchool_data',
FILENAME='E:\Database\MySchool_data.mdf',
SIZE=10MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
)
LOG ON
(
NAME='MySchool_log.ldf',
FILENAME='E:\Database\MySchool_log.ldf',
SIZE=3MB,
MAXSIZE=20MB,
FILEGROWTH=10%
)
GO

USE MySchoolDB
--创建学生表（student）
IF OBJECT_ID('student') IS NOT NULL
BEGIN DROP TABLE student END
CREATE TABLE student(
st_id VARCHAR(9)  NOT NULL,	--学生学号
st_nm VARCHAR(8) NOT NULL,	--学生姓名
st_sex VARCHAR(2) NULL,	--学生性别
st_birth DATETIME,	--出生日期
st_score INT,		--入学成绩
st_date DATETIME,	--入学日期
st_from VARCHAR(20),	--学生来源
st_dpid VARCHAR(2),	--所在系编号
st_mnt TINYINT		--学生职务
)
ALTER TABLE student ADD CONSTRAINT PK_student_st_id PRIMARY KEY(st_id)	--学生学号为主键（不是标识列）
GO

--创建课程信息表（couse）
IF OBJECT_ID('couse') IS NOT NULL
BEGIN DROP TABLE couse END
CREATE TABLE couse(
cs_id VARCHAR(4) NOT NULL,	--课程编号
cs_nm VARCHAR(20) NOT NULL,	--课程名称
cs_tm INT,	--课程学时
cs_sc INT	--课程学分
)
ALTER TABLE couse ADD CONSTRAINT PK_couse_cs_id PRIMARY KEY(cs_id)	--课程编号为主键
GO

--创建选课表（slt_couse）
IF OBJECT_ID('slt_couse') IS NOT NULL
BEGIN DROP TABLE slt_couse END
CREATE TABLE slt_couse(
cs_id VARCHAR(4) NOT NULL,	--课程编号
st_id VARCHAR(9) NOT NULL,	--学生编号
score INT, --课程成绩
sltdate DATETIME--选课日期
)
ALTER TABLE slt_couse ADD CONSTRAINT FK_slt_couse_cs_id FOREIGN KEY(cs_id) REFERENCES couse(cs_id)--课程编号外键
ALTER TABLE slt_couse ADD CONSTRAINT FK_slt_couse_st_id FOREIGN KEY(st_id) REFERENCES student(st_id)--学生编号外键
GO

--创建院系信息表
IF OBJECT_ID('dept') IS NOT NULL
BEGIN DROP TABLE dept END
CREATE TABLE dept(
dp_id VARCHAR(2) NOT NULL,	--系编号
dp_nm VARCHAR(20)  NOT NULL,	--院系名称
dp_drt VARCHAR(8),	--院系主任
dp_tel VARCHAR(12) --联系电话
)
GO

--【表中插入数据】
--操作3.1：向dept表插入一条记录，系号11，系名自动控制系，系主任为李其余，电话81234567
DELETE dept
INSERT INTO dept VALUES(11,'自动控制系','李其余',81234567)
INSERT INTO dept VALUES(10,'中国语言文学系','许军',63603897)
INSERT INTO dept VALUES(9,'文物和博物馆系','杨小燕',57303370)
INSERT INTO dept VALUES(8,'世界经济系','汪自燕',57928305)
INSERT INTO dept VALUES(7,'日语语言文学系','甄杰',57928312)
INSERT INTO dept VALUES(6,'微电子学系','徐燕',57928315)
INSERT INTO dept VALUES(5,'电子工程系','李晓丽',57928409)
INSERT INTO dept VALUES(6,'力学与工程科学系','李圆圆',57928407)
INSERT INTO dept VALUES(5,'高分子科学系','孔梅',57928390)

--操作3.2：向student表插入一条记录，学号070201001，姓名为王小五，性别为男，出生日期为1990年9月9日，系号为11，其余字段为NULL或默认值
DELETE student WHERE st_id<>070201001
INSERT INTO student(st_id,st_nm,st_sex,st_birth,st_dpid) VALUES('070201001','王小五','男',1990-9-9,11)
INSERT INTO student(st_id,st_nm,st_sex,st_birth,st_score,st_date,st_from,st_dpid,st_mnt)
SELECT '070201002','刘瑞卿','男',1996-03-14,92,2017-03-14,'巢湖市无为县姚沟镇',11,111 UNION
SELECT '070201003','洪宁波','男',1998-07-18,80,2017-03-14,'北京市顺义区北石槽镇',10,222 UNION
SELECT '070201004','易东杰','男',1999-06-06,67,2017-03-14,'北京市县延庆县永宁镇',9,100 UNION
SELECT '070201005','杨利杰','男',1997-10-10,59,2017-03-14,'天津市西青区南河镇',8,99 UNION
SELECT '070201006','蔡梦蝶','女',1999-07-03,54,2017-03-14,'河北省石家庄市',7,88 UNION
SELECT '070201007','张近超','男',2000-02-22,100,2017-03-14,'河北省衡水市景县',6,77 UNION
SELECT '070201008','张国强','男',1998-02-14,78,2017-03-14,'合肥市包河区',5,66 UNION
SELECT '070201009','杨华','男',1996-03-14,85,2017-03-14,'池州市青阳县',4,55 

--操作3.3：向couse表插入一条记录，课程号1234，课程名为操作系统，其余字段为NULL或默认值
DELETE couse
INSERT INTO couse VALUES('1234','操作系统',NULL,NULL)
INSERT INTO couse(cs_id,cs_nm,cs_tm,cs_sc)
SELECT '1235','大学英语 1A',3,100 UNION
SELECT '1236','大学计算机基础',2,100 UNION
SELECT '1237','中国近现代史纲要',5,100 UNION
SELECT '1238','高等数学方法',8,100 UNION
SELECT '1239','有机化学',11,100 UNION
SELECT '1240','管理心理学',6,100 UNION
SELECT '1241','军事理论',5,100 UNION
SELECT '1242','大学化学实验(Ⅱ)',2,100

--操作3.4：向slt_couse表插入一条记录，课程号1234，学名070201001，其余字段为NULL或默认值
DELETE slt_couse
INSERT INTO slt_couse VALUES('1234','070201001',NULL,NULL)
INSERT INTO slt_couse(cs_id,st_id,score,sltdate)
SELECT '1235','070201002',98,2018-8-28 UNION
SELECT '1236','070201003',78,2018-8-28 UNION
SELECT '1237','070201004',94,2018-8-28 UNION
SELECT '1238','070201005',83,2018-8-28 UNION
SELECT '1239','070201006',90,2018-8-28 UNION
SELECT '1240','070201007',73,2018-8-28 UNION
SELECT '1241','070201008',65,2018-8-28 UNION
SELECT '1242','070201009',88,2018-8-28 


--4．修改表中数据
--操作4.1：修改student表记录，将王小五的入学成绩改为88
UPDATE student SET st_score=88 WHERE st_nm='王小五'

--操作4.2：修改couse表记录，将所有记录的学分改为4，学时改为64
UPDATE couse set cs_tm=64,cs_sc=4

--操作4.3：修改slt_couse表记录，将课程号为1234，学名为070201001的记录的成绩改为77
UPDATE slt_couse SET score=77 WHERE cs_id='1234' AND st_id='070201001'

--5．删除表中数据
--操作5.1：删除slt_couse表记录，将课程号为1234，学名为070201001的记录删除
DELETE FROM slt_couse where cs_id='1234' AND st_id='070201001'

--操作5.2：删除couse表记录，将课程号为1234的记录删除
DELETE FROM couse WHERE cs_id='1234'

--6.简单查询
--（1）查询表中所有的列
--操作6.1：查询所有系的信息
SELECT * FROM dept

--（2）查询表中指定列的信息
--操作6.2：查询所有的课程号与课程名称
SELECT cs_id,cs_nm FROM couse

--（3）在查询列表中使用列表达式
--操作6.3：在查询student表时使用列表达式：入学成绩+400
SELECT st_id ,st_nm ,st_score ,st_score+400 as NewScore FROM student

--（4）重新命名查询结果
--操作6.4：使用AS关键字为dept表中属性指定列名：系号、系名、系主任、联系电话
SELECT dp_id AS 系号,dp_nm AS 系名,dp_drt AS 系主任,dp_tel AS 联系电话 from dept

--操作6.5：使用"="号为couse表中属性指定列名：课程号、课程名、学时(=cs_sc*16)、学分
SELECT 课程号=cs_id,课程名=cs_nm,学分=cs_sc,学时=cs_sc*16 from couse

--（5）增加说明列
--操作6.6：查询dept表的系号、系名和系主任，向查询结果中插入说明列：系号、系名和系主任
SELECT '系号',dp_id,'系名',dp_nm,'系主任',dp_drt from dept

--（6）查询列表中使用系统函数
--操作6.7：显示所有学生的学号、姓名、性别和入学年份
SELECT st_id, st_nm, st_sex, DATEPART(yy,st_birth) AS 入学年份 FROM student

--操作6.8：显示所有学生学号、姓名、性别和班级(学号前6位)
SELECT TOP 6 st_id, st_nm, st_sex FROM student

--（7）消除查询结果中的重复项
--操作6.9：显示所有学生班级(院系)
SELECT st_nm,st_dpid FROM student WHERE st_dpid IN(SELECT dp_id FROM dept)

--（8）取得查询结果的部分行集
--操作6.10：显示前5条学生记录信息
SELECT TOP 5 * FROM student

--操作6.11：显示前25%条学生记录信息
SELECT TOP 25 PERCENT * FROM student

--7．条件查询
--（1）使用关系表达式表示查询条件
--操作7.1：查询dept表中系号为11的院系信息
SELECT * FROM dept WHERE dp_id = '11'

--操作7.2：查询student表中11系的学生学号、姓名、性别和所在系编号
SELECT st_id, st_nm, st_sex, st_dpid FROM student WHERE st_dpid = '11'

--操作7.3：查询student表中2008年及以后入学的学生信息
SELECT * FROM student WHERE DATEPART( yy, st_date )>= 1905

--操作7.4：在查询student表080808班学生的学号、姓名、性别和入学成绩
SELECT st_id, st_nm, st_sex, st_score FROM student WHERE st_id='070201002'
SELECT * FROM student
--（2）使用逻辑表达式表示查询条件
--操作7.5：查询student表中非11系的学生信息
SELECT * FROM student WHERE NOT (st_dpid = '11')

--操作7.6：查询选修了1002号课程且成绩在60以下的学生学号
SELECT st_id FROM slt_couse WHERE (cs_id='1238') AND (score<60)
SELECT * FROM student

--操作7.7：查询2007年入学的11系所有男生信息
SELECT * FROM student WHERE DATEPART(yy,st_date)=1905 AND st_dpid='11' AND st_sex='男'

--操作7.8：查询11系和12系的学生信息
SELECT * FROM student WHERE st_dpid='11' OR st_dpid='12'

--操作7.9：查询11系和12系所有2007年入学的学生信息
SELECT * FROM  student WHERE (st_dpid='11' OR st_dpid='12') AND DATEPART(yy,st_date)=1905

--（3）使用LIKE关键字进行模糊查询
--操作7.10：查询所有“计算机”开头的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '计算机%'

--操作7.11：查询所有由三个字组成的“王”姓学生信息
SELECT * FROM student WHERE st_nm LIKE '王__'

--操作7.12：查询所有课程名中包含“信息”的课程信息
SELECT * FROM couse WHERE cs_nm LIKE '%计算机%'

--操作7.13：查询学生姓名介于王姓到张姓的信息
SELECT * FROM student WHERE st_nm LIKE '[王-张]%'

--（4）使用Between…And关键字进行查询
--操作7.14：查询在1989.7.1到1990.6.30之间出生的学生信息
SELECT st_id, st_nm, st_sex, st_birth FROM student WHERE st_birth BETWEEN '1904.7.1' AND '1999.6.30'

--操作7.15：查询选修了1001号课程且成绩在60到80之间的学生选课信息
SELECT * FROM slt_couse WHERE cs_id='1236' AND (score BETWEEN 60 AND 80)

--（5）使用IN关键字进行查询
--操作7.16：查询11系、12系、13系的学生信息
SELECT * FROM student WHERE st_dpid IN ('11', '10','9')

--操作7.17：查询所有张，王，李，赵姓的学生的学号、姓名、性别
SELECT st_id, st_nm, st_sex FROM student WHERE Left(st_nm,1) IN ('张','王','李','赵')

--（6）使用[NOT] NULL关键字进行查询
--操作7.18：查询所有生源为非空的学生信息
SELECT * FROM  student WHERE  st_from IS NOT NULL

--操作7.19：查询选修了1001号课程且成绩为空的学生选课信息
SELECT * FROM slt_couse WHERE cs_id='1238' AND score IS NULL

--8.查询排序与查询结果存储
--操作8.1：查询课程信息，按课程名称降序排序
SELECT * FROM couse ORDER BY cs_nm DESC

--操作8.2：查询选修了1001号课程成绩非空的学生学号和成绩，并按成绩降序排序
SELECT st_id, score FROM slt_couse WHERE cs_id='1240' AND score IS NOT NULL
ORDER BY score DESC

--操作8.3：查询11系学生学号、姓名和年龄，按年龄升序排序
SELECT st_id,st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS age FROM  student
ORDER BY age ASC

--操作8.4：查询学生信息，按姓名升序排序，再按系号降序排序
SELECT * FROM student ORDER BY st_nm, st_dpid DESC

--操作8.5：创建学生表副本student01，仅保留学生学号、姓名和性别
SELECT st_id, st_nm, st_sex INTO  student01  FROM  student

--操作8.6：查询陕西籍学生，将结果保存在新表st_shanxi
SELECT * INTO st_shanxi FROM  student WHERE  st_from='%安徽省%'

--操作8.7：查询选修了1001号课程学生的选课信息，按学号升序排序，将结果保存在新表slt1001
SELECT * INTO slt1001 FROM slt_couse WHERE cs_id='1240' ORDER BY st_id

--9.查询统计与汇总
--操作9.1：查询课程总数
SELECT  COUNT( * ) FROM  couse

--操作9.2：查询选修1001号课程的学生人数
SELECT  COUNT(st_id) FROM  slt_couse WHERE  cs_id = '1234'

--操作9.3：查询被选修课程的数量
SELECT  COUNT( DISTINCT cs_id )  FROM  slt_couse

--操作9.4：查询选修070101班学生的平均入学成绩
SELECT  AVG(score) FROM slt_couse WHERE cs_id='070201002'
SELECT * FROM slt_couse 
--操作9.5：查询070101001号学生选修课程的数量、总分以及平均分
SELECT  COUNT(cs_id) AS 课程数量,SUM(score) AS 总分,AVG(score) AS 平均分 FROM  slt_couse WHERE  st_id='070101001'

--操作9.6：查询选修1001号课程的学生人数、最高分、最低分和平均分
SELECT COUNT(*)学生人数,MAX(score)最高分, MIN(score)最低分, AVG (score) AS 平均分 FROM  slt_couse WHERE  cs_id='1235'

--操作9.7：求各个课程号和相应的选课人数
SELECT  cs_id, COUNT(st_id)FROM  slt_couse GROUP BY cs_id

--操作9.8：统计各班人数
SELECT  LEFT(st_id,6) AS 班级, COUNT(st_id) AS 人数
FROM  student
GROUP  BY  LEFT(st_id,6)


--操作9.9：依次按班级、系号对学生进行分类统计人数、入学平均分
SELECT  st_dpid AS 系号, LEFT(st_id,6) AS 班级, 
COUNT(st_nm) AS 人数, AVG(st_score) AS 均分
FROM  student  
GROUP  BY LEFT(st_id,6), st_dpid

--操作9.10：查询选修了均分在75以上的课程号及均分
SELECT cs_id AS 课程编号, AVG(score) AS 均分
FROM  slt_couse
GROUP  BY cs_id HAVING AVG(score)>75

--操作9.11：查询选修了2门以上课程的学生学号
SELECT  st_id FROM slt_couse GROUP  BY st_id HAVING COUNT(*)>2

--操作9.12：明细汇总年龄<20的学生，并汇总学生数量、平均年龄
SELECT  st_nm,DATEPART(yy,GETDATE( ))-DATEPART(yy,st_birth) AS 年龄 FROM  student
WHERE  DATEPART(yy,GETDATE())-DATEPART(yy,st_birth)<20
--COMPUTE COUNT(st_nm),AVG(DATEPART(yy,GETDATE())-DATEPART(yy,st_birth))

--操作9.13：按班级明细汇总成绩<85分的学生，汇总学生数、均分
SELECT COUNT(st_nm),AVG(st_score) 平均分 FROM  student WHERE  st_score<85
ORDER  BY st_score 
--COMPUTE  COUNT(st_nm), AVG(st_score) BY 班级


--10.连接查询
--操作10.1：用SQL Server形式连接查询学生学号、姓名、性别及其所选课程编号
SELECT  a.st_id, st_nm, st_sex, cs_id FROM  student a, slt_couse b WHERE  a.st_id = b.st_id
ORDER  BY  a.st_id

--操作10.2：用内连接查询学生学号、姓名、性别及其所选课程编号
SELECT  a.st_id, st_nm, st_sex, cs_id FROM  student a INNER JOIN slt_couse b ON  a.st_id = b.st_id
ORDER  BY a.st_id

--操作10.3：用SQL Server形式连接查询学生学号、姓名及其所选课程名称及成绩
SELECT  a.st_id, st_nm, cs_nm, score FROM  student a, slt_couse b, couse c WHERE  a.st_id = b.st_id AND b.cs_id = c.cs_id
ORDER  BY a.st_id


--操作10.4：用内连接查询学生学号、姓名及其所选课程名称及成绩
SELECT  a.st_id, st_nm, cs_nm, score
FROM  slt_couse a INNER JOIN student b ON a.st_id = b.st_id
INNER  JOIN couse c ON a.cs_id = c.cs_id
ORDER  BY  b.st_id

--操作10.5：查询选修了1002课程的学生学号、姓名及1001课程成绩
SELECT  a.st_id, st_nm, score FROM  student a,slt_couse b WHERE  a.st_id = b.st_id AND b.cs_id = '1236'
ORDER  BY b.st_id

--操作10.6：查询选修了“数据结构”课程的学生学号、姓名及课程成绩
SELECT  a.st_id, st_nm, score FROM  student a, slt_couse b, couse c
WHERE  a.st_id=b.st_id AND b.cs_id=c.cs_id AND c.cs_nm='有机化学'
ORDER  BY  a.st_id
select * from couse

--操作10.7：用左外连接查询没有选修任何课程的学生学号、姓名
SELECT  a.st_id, st_nm, score
FROM  student a LEFT OUTER JOIN slt_couse b ON a.st_id = b.st_id
WHERE  b.cs_id IS NULL
ORDER  BY b.st_id

--操作10.8：用右外连接查询选修各个课程的学生学号
SELECT  b.cs_id, a.st_id FROM  slt_couse a Right OUTER JOIN couse b ON a.cs_id = b.cs_id 
ORDER  BY  b.cs_id








